In [130]:
from IPython.display import display_html
from IPython.display import HTML, display
import pprint
font = "Roboto-Regular.ttf"
pp = pprint.PrettyPrinter(indent=4, width=100)
HTML('''<script>
code_show=true;
function code_toggle() {
if (code_show){
$('div.input').hide();
} else {
$('div.input').show();
}
code_show = !code_show
}
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')
Out[130]:
In [17]:
from IPython.display import Image
Image(filename='identity.png')
Out[17]:
In [22]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">EXECUTIVE SUMMARY </span></div>
In [4]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Well-developed countries have high-end and desirable amenities which translates to high standards of living, in concurrence with the core idea of Spatial Equilibrium. Studies have documented the strong connection between the quality of social infrastructure and the well being of residents, yet evidence on the connection of existence of decent amenities to economic growth has yet to be developed.
<br>
<br>
As such, this project intended to identify the association rules among available amenities – both public and commercial ones – in ASEAN countries. Particularly, we answered the question “Will the association rules of amenities reflect the countries’ economic conditions?” We used the OpenStreetMap dataset from the Registry of Open Data in AWS with more than 300,000 amenities from 10 ASEAN countries.
<br>
<br>
Through Amazon Athena, the data were queried using the geographic boundaries of the countries and compiled at a 1x1 km resolution based on Global Soil Partnership of the Food and Agriculture Organization of the United Nations. Frequent itemset mining was employed to retrieve the amenities that were present in a certain country. To uncover the association rules, we compared the confidence and applied a minimum support of 1%. Lastly, we performed economic referencing using key indicators like gross domestic product (GDP) per capita, life expectancy, and ease of doing business.
<br>
<br>
We found out that Singapore consistently had the highest confidence in the uncovered association rules. We also observed an association between the relative economic indicator standing and the relationship of entertainment amenities to transportation, health and financial amenities.
<br>
<br>
Based on the above results, we recommend that developing countries look into township development and its contribution to an area’s economic development, be it urban or rural. As for the Philippines, we suggest that the government focus on improving the quality of life of its citizens by providing ample and decent amenities nearby entertainment centers for this is linked to improving growth.
<div>
In [23]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">INTRODUCTION</span></div>
In [1]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Economic growth has been defined in various ways. It has been conventionally measured through the percentage increase in Gross Domestic Product (GDP). However, it does not mean that the greater rate is much better than the lower one because different countries have specific economic background and development speed. Meanwhile, other literatures considered quality of governance, technological progress, ease of doing business, human development capacity as primary indicators of economic growth.
<br>
<br>
Using big data, this study attempted to introduce a new way of comparing countries’ economic condition by comparing amenities situated in the ASEAN nations. While economic growth and natural resource management literature have acknowledged the importance of amenity attributes in economic performance and more widely quality of life, the nature of the linkage is not well understood. In 2001, Deller attempted to create an empirical model to connect the role of natural resource amenity attributes (i.e., five broad-based indices of amenity and quality of life attributes: climate, land, water, winter recreation, and developed recreational infrastructure) in fostering regional economic growth in the United States. It was found that all five amenity attributes are positively related to at least one measure of growth.
<br>
<br>
Unlike Deller, this study focused on discovering the association rules of amenities in ASEAN countries and linking such to relative economic performance of said countries based on currently available indices. The ASEAN region was chosen given that this is an emerging region with countries from different segments of development.
<div>
In [24]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">BUSINESS VALUE</span></div>
In [8]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
The study can help in spatial planning recommendations in order to promote:
<br>
<br>
a. more balanced accessibility and participation; <br>
b. better quality of life; and <br>
c. well-planned business agglomeration
<div>
In [25]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">METHODOLOGY</span></div>
1. Data Collection¶
1.1. Querying OpenStreetMap with Amazon Athena¶
Reference: https://aws.amazon.com/blogs/big-data/querying-openstreetmap-with-amazon-athena/
1.2. Registering OSM Table¶
The OSM Public Datasets consist of three tables:
- planet
Contains the current versions of all elements present in OSM. - planet_history
Contains a historical record of all versions of all elements (even those that have been deleted). - changesets
Contains information about changesets in which elements were modified (and which have a foreign key relationship to both the planet and planet_history tables).
We only used the planet table, so we only need to register one table using the code below. The size of the latest planet table in ORC format is 63.6Gb.
CREATE EXTERNAL TABLE planet ( id BIGINT, type STRING, tags MAP<STRING,STRING>, lat DECIMAL(9,7), lon DECIMAL(10,7), nds ARRAY<STRUCT<ref: BIGINT>>, members ARRAY<STRUCT<type: STRING, ref: BIGINT, role: STRING>>, changeset BIGINT, timestamp TIMESTAMP, uid BIGINT, user STRING, version BIGINT ) STORED AS ORCFILE LOCATION 's3://osm-pds/planet/';
1.3. Querying planet Table¶
Collect amenity nodes from planet table using the query below.
SELECT * from planet WHERE type = 'node' AND tags['amenity'] IN ('bar', 'bbq', 'biergarten', 'cafe', 'drinking_water', 'fast_food', 'food_court', 'ice_cream', 'pub','restaurant','college', 'driving_school', 'kindergarten', 'language_school', 'library', 'music_school', 'school', 'university','bicycle_parking', 'bicycle_repair_station', 'bicycle_rental', 'boat_rental', 'boat_sharing', 'bus_station', 'car_rental', 'car_sharing', 'car_wash', 'vehicle_inspection', 'charging_station', 'ferry_terminal', 'fuel', 'grit_bin', 'motorcycle_parking', 'parking', 'parking_entrance', 'parking_space', 'taxi', 'atm', 'bank', 'bank', 'bureau_de_change', 'baby_hatch', 'clinic', 'dentist', 'doctors', 'hospital', 'nursing_home', 'pharmacy', 'social_facility', 'veterinary', 'arts_centre', 'brothel', 'casino', 'cinema', 'community_centre', 'fountain', 'gambling', 'nightclub', 'planetarium', 'public_bookcase', 'social_centre', 'stripclub', 'studio', 'swingerclub', 'theatre','animal_boarding', 'animal_shelter', 'baking_oven', 'bench', 'childcare', 'clock', 'courthouse', 'crematorium', 'dive_centre', 'embassy', 'fire_station', 'grave_yard', 'hunting_stand', 'internet_cafe', 'kitchen', 'kneipp_water_cure', 'marketplace', 'monastery', 'photo_booth', 'place_of_worship', 'police', 'post_box', 'post_depot', 'post_office', 'prison', 'public_bath', 'ranger_station', 'recycling', 'sanitary_dump_station', 'shelter', 'shower', 'telephone', 'toilets', 'townhall','vending_machine', 'waste_basket', 'waste_disposal', 'waste_transfer_station', 'watering_place', 'water_point', 'user_defined') AND lon BETWEEN 117.17427453 AND 126.537423944 AND lat BETWEEN 5.58100332277 AND 18.5052273625;
Amenities used in the query above were based on the Map Features page of the OpenStreenMap Wiki under Amenity (https://wiki.openstreetmap.org/wiki/Map_Features#Amenity).
We run this query for each of the 10 country, each time we change the range of lat and lon from the query above. These ranges are corresponds to a country's bounding box which was based on a list of bounding boxes found here: https://gist.github.com/graydon/11198540. According to the authoer of the list, graydon, the information were extracted from http//www.naturalearthdata.com/download/110m/cultural/ne_110m_admin_0_countries.zip.
Once the query above is executed, the results were saved as a CSV file in a S3 bucket specified when setting up Athena. CSV files for the 10 countries can be found in the folder athena-query_results/
2. Data Preparation¶
Data collected from OpenStreetMap using Athena contains columns id, type, tags, lat, lon, nds, members, changeset, timestamp, uid, user, and version but not all of them were useful in this study. We only needed columns lat and lon to know the node's position on the map, and column tags which contains information on what amenity a particular node is.
We defined a funtion amenity which extracted the name of the amenity of a node. The function amen_type, then, classified the amenity name into one of the following amenity types: health, food, education, transportation, financial, entertainment, safety, animal_care, law, religious, and sanitation. These were based on the OpenStreetMap wiki's Map Features and can be viewed using this link: https://wiki.openstreetmap.org/wiki/Map_Features#Amenity.
2.1. Utility Functions for Determining Amenity Types¶
The following utility functions were formulated:
a. amenity to extract amenity name from tags column. This returns the amenity name of the node.
b. amen_type to classify amenities into amenity types. This returns one of the following amenity types: health, food, education, transportation, financial, entertainment, safety, animal_care, law, religious, and sanitation. Amenity types are based on the OpenStreetMap wiki (https://wiki.openstreetmap.org/wiki/Map_Features#Amenity)
In [10]:
import re
def amenity(tag):
'''Extract amenity name from `tags` column
Parameters:
------
tag: string
Node tag extracted from OpenStreetMap
Returns:
--------
amen: string
The amenity name of the node
'''
if tag == '{}':
return None
amen = re.findall("amenity=(.*?)[,}]", tag)
if amen:
return amen[0]
else:
return None
def amen_type(amen):
'''Classify amenities into amenity types.
Parameters:
-----------
amen: string
Amenity name of node
Returns:
--------
amenity type: string
Returns one of the following amenity types: health, food, education,
transportation, financial, entertainment, safety, animal_care, law,
religious, and sanitation. Amenity types are based on the
OpenStreetMap wiki (https://wiki.openstreetmap.org/wiki/Map_Features#A
menity)
'''
health = ['clinic', 'hospital', 'doctors', 'baby_hatch', 'dentist',
'nursing_home', 'pharmacy', 'social_facility', 'veterinary']
food = ['bar', 'bbq', 'biergarten', 'cafe', 'drinking_water',
'fast_food', 'food_court', 'ice_cream', 'pub', 'restaurant',
'marketplace', 'vending_machine', 'water_point']
education = ['college', 'driving_school', 'kindergarten',
'language_school', 'library', 'music_school', 'school',
'university']
transportation = ['bicycle_parking', 'bicycle_repair_station',
'bicycle_rental', 'boat_rental', 'boat_sharing',
'bus_station', 'car_rental', 'car_sharing', 'car_wash',
'vehicle_inspection', 'charging_station',
'ferry_terminal', 'fuel', 'grit_bin',
'motorcycle_parking', 'parking', 'parking_entrance',
'parking_space', 'taxi']
financial = ['atm', 'bank', 'bureau_de_change']
entertainment = ['arts_centre', 'brothel', 'casino', 'cinema',
'community_centre', 'fountain', 'gambling', 'nightclub',
'planetarium', 'public_bookcase', 'social_centre',
'stripclub', 'studio', 'swingerclub', 'theatre',
'dive_centre', 'hunting_stand', 'internet_cafe',
'photo_booth']
safety = ['firestation', 'police', 'ranger_station', 'shelter']
animal_care = ['animal_boarding', 'animal_shelter', 'watering_place']
law = ['courthouse', 'embassy', 'prison', 'townhall']
religious = ['monastery', 'place_of_worship']
sanitation = ['recycling', 'sanitary_dump_station', 'toilets',
'waste_basket', 'waste_disposal', 'waste_transfer_station']
if amen in health:
return 'health'
elif amen in food:
return 'food'
elif amen in education:
return 'education'
elif amen in transportation:
return 'transportation'
elif amen in financial:
return 'financial'
elif amen in entertainment:
return 'entertainment'
elif amen in safety:
return 'safety'
elif amen in animal_care:
return 'animal_care'
elif amen in law:
return 'law'
elif amen in religious:
return 'religious'
elif amen in safety:
return 'sanitation'
else:
return 'others'
2.2. Bounding boxes¶
The bounding boxes in the form of a combination of longitude and latitude coordinates were used to define a country's borders. These bounding boxes help determine if the amenity is inside the country's territory and shall help in distinguishing the country from its neighbors.
In [11]:
import json
# country bounding boxes
boxes = {'ABW': {'sw': {'lat': 12.1702998, 'lon': -70.2809842}, 'ne': {'lat': 12.8102998, 'lon': -69.6409842}}, 'AFG': {'sw': {'lat': 29.3772, 'lon': 60.5176034}, 'ne': {'lat': 38.4910682, 'lon': 74.889862}}, 'AGO': {'sw': {'lat': -18.038945, 'lon': 11.4609793}, 'ne': {'lat': -4.3880634, 'lon': 24.0878856}}, 'AIA': {'sw': {'lat': 18.0615454, 'lon': -63.6391992}, 'ne': {'lat': 18.7951194, 'lon': -62.7125449}}, 'ALA': {'sw': {'lat': 59.4541578, 'lon': 19.0832098}, 'ne': {'lat': 60.87665, 'lon': 21.3456556}}, 'ALB': {'sw': {'lat': 39.6448625, 'lon': 19.1246095}, 'ne': {'lat': 42.6610848, 'lon': 21.0574335}}, 'AND': {'sw': {'lat': 42.4288238, 'lon': 1.4135781}, 'ne': {'lat': 42.6559357, 'lon': 1.7863837}}, 'ANT': {'sw': {'lat': 12.1544542, 'lon': -68.940593}, 'ne': {'lat': 12.1547472, 'lon': -68.9403518}}, 'ARE': {'sw': {'lat': 22.6444, 'lon': 51.498}, 'ne': {'lat': 26.2822, 'lon': 56.3834}}, 'ARG': {'sw': {'lat': -55.1850761, 'lon': -73.5600329}, 'ne': {'lat': -21.781168, 'lon': -53.6374515}}, 'ARM': {'sw': {'lat': 38.8404775, 'lon': 43.4471395}, 'ne': {'lat': 41.300712, 'lon': 46.6333087}}, 'ASM': {'sw': {'lat': -14.7608358, 'lon': -171.2951296}, 'ne': {'lat': -10.8449746, 'lon': -167.9322899}}, 'ATA': {'sw': {'lat': -85.0511287, 'lon': -180.0}, 'ne': {'lat': -60.0, 'lon': 180.0}}, 'ATF': {'sw': {'lat': -50.2187169, 'lon': 39.4138676}, 'ne': {'lat': -11.3139928, 'lon': 77.8494974}}, 'ATG': {'sw': {'lat': 16.7573901, 'lon': -62.5536517}, 'ne': {'lat': 17.929, 'lon': -61.447857}}, 'AUS': {'sw': {'lat': -55.3228175, 'lon': 72.2460938}, 'ne': {'lat': -9.0882278, 'lon': 168.2249543}}, 'AUT': {'sw': {'lat': 46.3722761, 'lon': 9.5307487}, 'ne': {'lat': 49.0205305, 'lon': 17.160776}}, 'AZE': {'sw': {'lat': 38.3929551, 'lon': 44.7633701}, 'ne': {'lat': 41.9502947, 'lon': 51.0090302}}, 'BDI': {'sw': {'lat': -4.4693155, 'lon': 29.0007401}, 'ne': {'lat': -2.3096796, 'lon': 30.8498462}}, 'BEL': {'sw': {'lat': 49.4969821, 'lon': 2.3889137}, 'ne': {'lat': 51.5516667, 'lon': 6.408097}}, 'BEN': {'sw': {'lat': 6.0398696, 'lon': 0.776667}, 'ne': {'lat': 12.4092447, 'lon': 3.843343}}, 'BFA': {'sw': {'lat': 9.4104718, 'lon': -5.5132416}, 'ne': {'lat': 15.084, 'lon': 2.4089717}}, 'BGD': {'sw': {'lat': 20.3756582, 'lon': 88.0075306}, 'ne': {'lat': 26.6382534, 'lon': 92.6804979}}, 'BGR': {'sw': {'lat': 41.2353929, 'lon': 22.3571459}, 'ne': {'lat': 44.2167064, 'lon': 28.8875409}}, 'BHR': {'sw': {'lat': 25.535, 'lon': 50.2697989}, 'ne': {'lat': 26.6872444, 'lon': 50.9233693}}, 'BHS': {'sw': {'lat': 20.7059846, 'lon': -80.7001941}, 'ne': {'lat': 27.4734551, 'lon': -72.4477521}}, 'BIH': {'sw': {'lat': 42.5553114, 'lon': 15.7287433}, 'ne': {'lat': 45.2764135, 'lon': 19.6237311}}, 'BLM': {'sw': {'lat': 17.670931, 'lon': -63.06639}, 'ne': {'lat': 18.1375569, 'lon': -62.5844019}}, 'BLR': {'sw': {'lat': 51.2575982, 'lon': 23.1783344}, 'ne': {'lat': 56.17218, 'lon': 32.7627809}}, 'BLZ': {'sw': {'lat': 15.8857286, 'lon': -89.2262083}, 'ne': {'lat': 18.496001, 'lon': -87.3098494}}, 'BMU': {'sw': {'lat': 32.0469651, 'lon': -65.1232222}, 'ne': {'lat': 32.5913693, 'lon': -64.4109842}}, 'BOL': {'sw': {'lat': -22.8982742, 'lon': -69.6450073}, 'ne': {'lat': -9.6689438, 'lon': -57.453}}, 'BRA': {'sw': {'lat': -33.8689056, 'lon': -73.9830625}, 'ne': {'lat': 5.2842873, 'lon': -28.6341164}}, 'BRB': {'sw': {'lat': 12.845, 'lon': -59.8562115}, 'ne': {'lat': 13.535, 'lon': -59.2147175}}, 'BRN': {'sw': {'lat': 4.002508, 'lon': 114.0758734}, 'ne': {'lat': 5.1011857, 'lon': 115.3635623}}, 'BTN': {'sw': {'lat': 26.702016, 'lon': 88.7464724}, 'ne': {'lat': 28.246987, 'lon': 92.1252321}}, 'BVT': {'sw': {'lat': -54.654, 'lon': 2.9345531}, 'ne': {'lat': -54.187, 'lon': 3.7791099}}, 'BWA': {'sw': {'lat': -26.9059669, 'lon': 19.9986474}, 'ne': {'lat': -17.778137, 'lon': 29.375304}}, 'CAF': {'sw': {'lat': 2.2156553, 'lon': 14.4155426}, 'ne': {'lat': 11.001389, 'lon': 27.4540764}}, 'CAN': {'sw': {'lat': 41.6765556, 'lon': -141.00275}, 'ne': {'lat': 83.3362128, 'lon': -52.3231981}}, 'CCK': {'sw': {'lat': -12.4055983, 'lon': 96.612524}, 'ne': {'lat': -11.6213132, 'lon': 97.1357343}}, 'CHE': {'sw': {'lat': 45.817995, 'lon': 5.9559113}, 'ne': {'lat': 47.8084648, 'lon': 10.4922941}}, 'CHL': {'sw': {'lat': -56.725, 'lon': -109.6795789}, 'ne': {'lat': -17.4983998, 'lon': -66.0753474}}, 'CHN': {'sw': {'lat': 8.8383436, 'lon': 73.4997347}, 'ne': {'lat': 53.5608154, 'lon': 134.7754563}}, 'CIV': {'sw': {'lat': 4.1621205, 'lon': -8.601725}, 'ne': {'lat': 10.740197, 'lon': -2.493031}}, 'CMR': {'sw': {'lat': 1.6546659, 'lon': 8.3822176}, 'ne': {'lat': 13.083333, 'lon': 16.1921476}}, 'COD': {'sw': {'lat': -13.459035, 'lon': 12.039074}, 'ne': {'lat': 5.3920026, 'lon': 31.3056758}}, 'COG': {'sw': {'lat': -5.149089, 'lon': 11.0048205}, 'ne': {'lat': 3.713056, 'lon': 18.643611}}, 'COK': {'sw': {'lat': -22.15807, 'lon': -166.0856468}, 'ne': {'lat': -8.7168792, 'lon': -157.1089329}}, 'COL': {'sw': {'lat': -4.2316872, 'lon': -82.1243666}, 'ne': {'lat': 16.0571269, 'lon': -66.8511907}}, 'COM': {'sw': {'lat': -12.621, 'lon': 43.025305}, 'ne': {'lat': -11.165, 'lon': 44.7451922}}, 'CPV': {'sw': {'lat': 14.8031546, 'lon': -25.3609478}, 'ne': {'lat': 17.2053108, 'lon': -22.6673416}}, 'CRI': {'sw': {'lat': 5.3329698, 'lon': -87.2722647}, 'ne': {'lat': 11.2195684, 'lon': -82.5060208}}, 'CUB': {'sw': {'lat': 19.6275294, 'lon': -85.1679702}, 'ne': {'lat': 23.4816972, 'lon': -73.9190004}}, 'CXR': {'sw': {'lat': -10.5698515, 'lon': 105.5336422}, 'ne': {'lat': -10.4123553, 'lon': 105.7130159}}, 'CYM': {'sw': {'lat': 19.0620619, 'lon': -81.6313748}, 'ne': {'lat': 19.9573759, 'lon': -79.5110954}}, 'CYP': {'sw': {'lat': 34.4383706, 'lon': 32.0227581}, 'ne': {'lat': 35.913252, 'lon': 34.8553182}}, 'CZE': {'sw': {'lat': 48.5518083, 'lon': 12.0905901}, 'ne': {'lat': 51.0557036, 'lon': 18.859216}}, 'DEU': {'sw': {'lat': 47.2701114, 'lon': 5.8663153}, 'ne': {'lat': 55.099161, 'lon': 15.0419319}}, 'DJI': {'sw': {'lat': 10.9149547, 'lon': 41.7713139}, 'ne': {'lat': 12.7923081, 'lon': 43.6579046}}, 'DMA': {'sw': {'lat': 15.0074207, 'lon': -61.6869184}, 'ne': {'lat': 15.7872222, 'lon': -61.0329895}}, 'DNK': {'sw': {'lat': 54.4516667, 'lon': 7.7153255}, 'ne': {'lat': 57.9524297, 'lon': 15.5530641}}, 'DOM': {'sw': {'lat': 17.2701708, 'lon': -72.0574706}, 'ne': {'lat': 21.303433, 'lon': -68.1101463}}, 'DZA': {'sw': {'lat': 18.968147, 'lon': -8.668908}, 'ne': {'lat': 37.2962055, 'lon': 11.997337}}, 'ECU': {'sw': {'lat': -5.0159314, 'lon': -92.2072392}, 'ne': {'lat': 1.8835964, 'lon': -75.192504}}, 'EGY': {'sw': {'lat': 22.0, 'lon': 24.6499112}, 'ne': {'lat': 31.8330854, 'lon': 37.1153517}}, 'ERI': {'sw': {'lat': 12.3548219, 'lon': 36.4333653}, 'ne': {'lat': 18.0709917, 'lon': 43.3001714}}, 'ESH': {'sw': {'lat': 20.556883, 'lon': -17.3494721}, 'ne': {'lat': 27.6666834, 'lon': -8.666389}}, 'ESP': {'sw': {'lat': 27.4335426, 'lon': -18.3936845}, 'ne': {'lat': 43.9933088, 'lon': 4.5918885}}, 'EST': {'sw': {'lat': 57.5092997, 'lon': 21.3826069}, 'ne': {'lat': 59.9383754, 'lon': 28.2100175}}, 'ETH': {'sw': {'lat': 3.397448, 'lon': 32.9975838}, 'ne': {'lat': 14.8940537, 'lon': 47.9823797}}, 'FIN': {'sw': {'lat': 59.4541578, 'lon': 19.0832098}, 'ne': {'lat': 70.0922939, 'lon': 31.5867071}}, 'FJI': {'sw': {'lat': -21.9434274, 'lon': 172.0}, 'ne': {'lat': -12.2613866, 'lon': -178.5}}, 'FLK': {'sw': {'lat': -53.1186766, 'lon': -61.7726772}, 'ne': {'lat': -50.7973007, 'lon': -57.3662367}}, 'FRA': {'sw': {'lat': 41.2632185, 'lon': -5.4534286}, 'ne': {'lat': 51.268318, 'lon': 9.8678344}}, 'FRO': {'sw': {'lat': 61.3915553, 'lon': -7.6882939}, 'ne': {'lat': 62.3942991, 'lon': -6.2565525}}, 'FSM': {'sw': {'lat': 0.827, 'lon': 137.2234512}, 'ne': {'lat': 10.291, 'lon': 163.2364054}}, 'GAB': {'sw': {'lat': -4.1012261, 'lon': 8.5002246}, 'ne': {'lat': 2.3182171, 'lon': 14.539444}}, 'GBR': {'sw': {'lat': 49.674, 'lon': -14.015517}, 'ne': {'lat': 61.061, 'lon': 2.0919117}}, 'GEO': {'sw': {'lat': 41.0552922, 'lon': 39.8844803}, 'ne': {'lat': 43.5864294, 'lon': 46.7365373}}, 'GGY': {'sw': {'lat': 49.4155331, 'lon': -2.6751703}, 'ne': {'lat': 49.5090776, 'lon': -2.501814}}, 'GHA': {'sw': {'lat': 4.5392525, 'lon': -3.260786}, 'ne': {'lat': 11.1748562, 'lon': 1.2732942}}, 'GIB': {'sw': {'lat': 36.100807, 'lon': -5.3941295}, 'ne': {'lat': 36.180807, 'lon': -5.3141295}}, 'GIN': {'sw': {'lat': 7.1906045, 'lon': -15.5680508}, 'ne': {'lat': 12.67563, 'lon': -7.6381993}}, 'GLP': {'sw': {'lat': 15.8320085, 'lon': -61.809764}, 'ne': {'lat': 16.5144664, 'lon': -61.0003663}}, 'GMB': {'sw': {'lat': 13.061, 'lon': -17.0288254}, 'ne': {'lat': 13.8253137, 'lon': -13.797778}}, 'GNB': {'sw': {'lat': 10.6514215, 'lon': -16.894523}, 'ne': {'lat': 12.6862384, 'lon': -13.6348777}}, 'GNQ': {'sw': {'lat': -1.6732196, 'lon': 5.4172943}, 'ne': {'lat': 3.989, 'lon': 11.3598628}}, 'GRC': {'sw': {'lat': 34.7006096, 'lon': 19.2477876}, 'ne': {'lat': 41.7488862, 'lon': 29.7296986}}, 'GRD': {'sw': {'lat': 11.786, 'lon': -62.0065868}, 'ne': {'lat': 12.5966532, 'lon': -61.1732143}}, 'GRL': {'sw': {'lat': 59.515387, 'lon': -74.1250416}, 'ne': {'lat': 83.875172, 'lon': -10.0288759}}, 'GTM': {'sw': {'lat': 13.6345804, 'lon': -92.3105242}, 'ne': {'lat': 17.8165947, 'lon': -88.1755849}}, 'GUF': {'sw': {'lat': 2.112222, 'lon': -54.60278}, 'ne': {'lat': 5.7507111, 'lon': -51.6346139}}, 'GUM': {'sw': {'lat': 13.182335, 'lon': 144.563426}, 'ne': {'lat': 13.706179, 'lon': 145.009167}}, 'GUY': {'sw': {'lat': 1.1710017, 'lon': -61.414905}, 'ne': {'lat': 8.6038842, 'lon': -56.4689543}}, 'HKG': {'sw': {'lat': 22.1193278, 'lon': 114.0028131}, 'ne': {'lat': 22.4393278, 'lon': 114.3228131}}, 'HMD': {'sw': {'lat': -53.394741, 'lon': 72.2460938}, 'ne': {'lat': -52.7030677, 'lon': 74.1988754}}, 'HND': {'sw': {'lat': 12.9808485, 'lon': -89.3568207}, 'ne': {'lat': 17.619526, 'lon': -82.1729621}}, 'HRV': {'sw': {'lat': 42.1765993, 'lon': 13.2104814}, 'ne': {'lat': 46.555029, 'lon': 19.4470842}}, 'HTI': {'sw': {'lat': 17.9099291, 'lon': -75.2384618}, 'ne': {'lat': 20.2181368, 'lon': -71.6217461}}, 'HUN': {'sw': {'lat': 45.737128, 'lon': 16.1138867}, 'ne': {'lat': 48.585257, 'lon': 22.8977094}}, 'IDN': {'sw': {'lat': -11.2085669, 'lon': 94.7717124}, 'ne': {'lat': 6.2744496, 'lon': 141.0194444}}, 'IMN': {'sw': {'lat': 54.0539576, 'lon': -4.7946845}, 'ne': {'lat': 54.4178705, 'lon': -4.3076853}}, 'IND': {'sw': {'lat': 6.5546079, 'lon': 68.1113787}, 'ne': {'lat': 35.6745457, 'lon': 97.395561}}, 'IOT': {'sw': {'lat': -7.6454079, 'lon': 71.036504}, 'ne': {'lat': -5.037066, 'lon': 72.7020157}}, 'IRL': {'sw': {'lat': 51.222, 'lon': -11.0133788}, 'ne': {'lat': 55.636, 'lon': -5.6582363}}, 'IRN': {'sw': {'lat': 24.8465103, 'lon': 44.0318908}, 'ne': {'lat': 39.7816502, 'lon': 63.3332704}}, 'IRQ': {'sw': {'lat': 29.0585661, 'lon': 38.7936719}, 'ne': {'lat': 37.380932, 'lon': 48.8412702}}, 'ISL': {'sw': {'lat': 63.0859177, 'lon': -25.0135069}, 'ne': {'lat': 67.353, 'lon': -12.8046162}}, 'ISR': {'sw': {'lat': 29.4533796, 'lon': 34.2674994}, 'ne': {'lat': 33.3356317, 'lon': 35.8950234}}, 'ITA': {'sw': {'lat': 35.2889616, 'lon': 6.6272658}, 'ne': {'lat': 47.0921462, 'lon': 18.7844746}}, 'JAM': {'sw': {'lat': 16.5899443, 'lon': -78.5782366}, 'ne': {'lat': 18.7256394, 'lon': -75.7541143}}, 'JEY': {'sw': {'lat': 49.1625179, 'lon': -2.254512}, 'ne': {'lat': 49.2621288, 'lon': -2.0104193}}, 'JOR': {'sw': {'lat': 29.183401, 'lon': 34.8844372}, 'ne': {'lat': 33.3750617, 'lon': 39.3012981}}, 'JPN': {'sw': {'lat': 20.2145811, 'lon': 122.7141754}, 'ne': {'lat': 45.7112046, 'lon': 154.205541}}, 'KAZ': {'sw': {'lat': 40.5686476, 'lon': 46.4932179}, 'ne': {'lat': 55.4421701, 'lon': 87.3156316}}, 'KEN': {'sw': {'lat': -4.8995204, 'lon': 33.9098987}, 'ne': {'lat': 4.62, 'lon': 41.899578}}, 'KGZ': {'sw': {'lat': 39.1728437, 'lon': 69.2649523}, 'ne': {'lat': 43.2667971, 'lon': 80.2295793}}, 'KHM': {'sw': {'lat': 9.4752639, 'lon': 102.3338282}, 'ne': {'lat': 14.6904224, 'lon': 107.6276788}}, 'KIR': {'sw': {'lat': -7.0516717, 'lon': -179.1645388}, 'ne': {'lat': 7.9483283, 'lon': -164.1645388}}, 'KNA': {'sw': {'lat': 16.895, 'lon': -63.051129}, 'ne': {'lat': 17.6158146, 'lon': -62.3303519}}, 'KOR': {'sw': {'lat': 32.9104556, 'lon': 124.354847}, 'ne': {'lat': 38.623477, 'lon': 132.1467806}}, 'KWT': {'sw': {'lat': 28.5243622, 'lon': 46.5526837}, 'ne': {'lat': 30.1038082, 'lon': 49.0046809}},
'LAO': {'sw': {'lat': 13.9096752, 'lon': 100.0843247}, 'ne': {'lat': 22.5086717, 'lon': 107.6349989}}, 'LBN': {'sw': {'lat': 33.0479858, 'lon': 34.8825667}, 'ne': {'lat': 34.6923543, 'lon': 36.625}}, 'LBR': {'sw': {'lat': 4.1555907, 'lon': -11.6080764}, 'ne': {'lat': 8.5519861, 'lon': -7.367323}}, 'LBY': {'sw': {'lat': 19.5008138, 'lon': 9.391081}, 'ne': {'lat': 33.3545898, 'lon': 25.3770629}}, 'LCA': {'sw': {'lat': 13.508, 'lon': -61.2853867}, 'ne': {'lat': 14.2725, 'lon': -60.6669363}}, 'LIE': {'sw': {'lat': 47.0484291, 'lon': 9.4716736}, 'ne': {'lat': 47.270581, 'lon': 9.6357143}}, 'LKA': {'sw': {'lat': 5.719, 'lon': 79.3959205}, 'ne': {'lat': 10.035, 'lon': 82.0810141}}, 'LSO': {'sw': {'lat': -30.6772773, 'lon': 27.0114632}, 'ne': {'lat': -28.570615, 'lon': 29.4557099}}, 'LTU': {'sw': {'lat': 53.8967893, 'lon': 20.653783}, 'ne': {'lat': 56.4504213, 'lon': 26.8355198}}, 'LUX': {'sw': {'lat': 49.4969821, 'lon': 4.9684415}, 'ne': {'lat': 50.430377, 'lon': 6.0344254}}, 'LVA': {'sw': {'lat': 55.6746505, 'lon': 20.6715407}, 'ne': {'lat': 58.0855688, 'lon': 28.2414904}}, 'MAC': {'sw': {'lat': 22.0766667, 'lon': 113.5281666}, 'ne': {'lat': 22.2170361, 'lon': 113.6301389}}, 'MAF': {'sw': {'lat': 17.8963535, 'lon': -63.3605643}, 'ne': {'lat': 18.1902778, 'lon': -62.7644063}}, 'MAR': {'sw': {'lat': 21.3365321, 'lon': -17.2551456}, 'ne': {'lat': 36.0505269, 'lon': -0.998429}}, 'MCO': {'sw': {'lat': 43.7247599, 'lon': 7.4090279}, 'ne': {'lat': 43.7519311, 'lon': 7.4398704}}, 'MDA': {'sw': {'lat': 45.4674139, 'lon': 26.6162189}, 'ne': {'lat': 48.4918695, 'lon': 30.1636756}}, 'MDG': {'sw': {'lat': -25.6071002, 'lon': 43.2202072}, 'ne': {'lat': -11.9519693, 'lon': 50.4862553}}, 'MDV': {'sw': {'lat': -0.9074935, 'lon': 72.3554187}, 'ne': {'lat': 7.3106246, 'lon': 73.9700962}}, 'MEX': {'sw': {'lat': 14.3886243, 'lon': -118.59919}, 'ne': {'lat': 32.7186553, 'lon': -86.493266}}, 'MHL': {'sw': {'lat': -0.5481258, 'lon': 163.4985095}, 'ne': {'lat': 14.4518742, 'lon': 178.4985095}}, 'MKD': {'sw': {'lat': 40.8536596, 'lon': 20.4529023}, 'ne': {'lat': 42.3735359, 'lon': 23.034051}}, 'MLI': {'sw': {'lat': 10.147811, 'lon': -12.2402835}, 'ne': {'lat': 25.001084, 'lon': 4.2673828}}, 'MLT': {'sw': {'lat': 35.6029696, 'lon': 13.9324226}, 'ne': {'lat': 36.2852706, 'lon': 14.8267966}}, 'MMR': {'sw': {'lat': 9.4399432, 'lon': 92.1719423}, 'ne': {'lat': 28.547835, 'lon': 101.1700796}}, 'MNE': {'sw': {'lat': 41.7495999, 'lon': 18.4195781}, 'ne': {'lat': 43.5585061, 'lon': 20.3561641}}, 'MNG': {'sw': {'lat': 41.5800276, 'lon': 87.73762}, 'ne': {'lat': 52.1496, 'lon': 119.931949}}, 'MNP': {'sw': {'lat': 14.036565, 'lon': 144.813338}, 'ne': {'lat': 20.616556, 'lon': 146.154418}}, 'MOZ': {'sw': {'lat': -26.9209427, 'lon': 30.2138197}, 'ne': {'lat': -10.3252149, 'lon': 41.0545908}}, 'MRT': {'sw': {'lat': 14.7209909, 'lon': -17.068081}, 'ne': {'lat': 27.314942, 'lon': -4.8333344}}, 'MSR': {'sw': {'lat': 16.475, 'lon': -62.450667}, 'ne': {'lat': 17.0152978, 'lon': -61.9353818}}, 'MTQ': {'sw': {'lat': 14.3948596, 'lon': -61.2290815}, 'ne': {'lat': 14.8787029, 'lon': -60.8095833}}, 'MUS': {'sw': {'lat': -20.725, 'lon': 56.3825151}, 'ne': {'lat': -10.138, 'lon': 63.7151319}}, 'MWI': {'sw': {'lat': -17.1296031, 'lon': 32.6703616}, 'ne': {'lat': -9.3683261, 'lon': 35.9185731}}, 'MYS': {'sw': {'lat': -5.1076241, 'lon': 105.3471939}, 'ne': {'lat': 9.8923759, 'lon': 120.3471939}}, 'MYT': {'sw': {'lat': -13.0210119, 'lon': 45.0183298}, 'ne': {'lat': -12.6365902, 'lon': 45.2999917}}, 'NAM': {'sw': {'lat': -28.96945, 'lon': 11.5280384}, 'ne': {'lat': -16.9634855, 'lon': 25.2617671}}, 'NCL': {'sw': {'lat': -23.2217509, 'lon': 162.6034343}, 'ne': {'lat': -17.6868616, 'lon': 167.8109827}}, 'NER': {'sw': {'lat': 11.693756, 'lon': 0.1689653}, 'ne': {'lat': 23.517178, 'lon': 15.996667}}, 'NFK': {'sw': {'lat': -29.333, 'lon': 167.6873878}, 'ne': {'lat': -28.796, 'lon': 168.2249543}}, 'NGA': {'sw': {'lat': 4.0690959, 'lon': 2.676932}, 'ne': {'lat': 13.885645, 'lon': 14.678014}}, 'NIC': {'sw': {'lat': 10.7076565, 'lon': -87.901532}, 'ne': {'lat': 15.0331183, 'lon': -82.6227023}}, 'NIU': {'sw': {'lat': -19.3548665, 'lon': -170.1595029}, 'ne': {'lat': -18.7534559, 'lon': -169.5647229}}, 'NLD': {'sw': {'lat': 50.7295671, 'lon': 1.9193492}, 'ne': {'lat': 53.7253321, 'lon': 7.2274985}}, 'NOR': {'sw': {'lat': 57.7590052, 'lon': 4.0875274}, 'ne': {'lat': 71.3848787, 'lon': 31.7614911}}, 'NPL': {'sw': {'lat': 26.3477581, 'lon': 80.0586226}, 'ne': {'lat': 30.446945, 'lon': 88.2015257}}, 'NRU': {'sw': {'lat': -0.5541334, 'lon': 166.9091794}, 'ne': {'lat': -0.5025906, 'lon': 166.9589235}}, 'NZL': {'sw': {'lat': -52.8213687, 'lon': -179.059153}, 'ne': {'lat': -29.0303303, 'lon': 179.3643594}}, 'OMN': {'sw': {'lat': 16.4649608, 'lon': 52}, 'ne': {'lat': 26.7026737, 'lon': 60.054577}}, 'PAK': {'sw': {'lat': 23.5393916, 'lon': 60.872855}, 'ne': {'lat': 37.084107, 'lon': 77.1203914}}, 'PAN': {'sw': {'lat': 7.0338679, 'lon': -83.0517245}, 'ne': {'lat': 9.8701757, 'lon': -77.1393779}}, 'PCN': {'sw': {'lat': -25.1306736, 'lon': -130.8049862}, 'ne': {'lat': -23.8655769, 'lon': -124.717534}}, 'PER': {'sw': {'lat': -20.1984472, 'lon': -84.6356535}, 'ne': {'lat': -0.0392818, 'lon': -68.6519906}}, 'PHL': {'sw': {'lat': 4.2158064, 'lon': 114.0952145}, 'ne': {'lat': 21.3217806, 'lon': 126.8072562}}, 'PLW': {'sw': {'lat': 2.748, 'lon': 131.0685462}, 'ne': {'lat': 8.222, 'lon': 134.7714735}}, 'PNG': {'sw': {'lat': -13.1816069, 'lon': 136.7489081}, 'ne': {'lat': 1.8183931, 'lon': 151.7489081}}, 'POL': {'sw': {'lat': 49.0020468, 'lon': 14.1229707}, 'ne': {'lat': 55.0336963, 'lon': 24.145783}}, 'PRI': {'sw': {'lat': 17.9268695, 'lon': -67.271492}, 'ne': {'lat': 18.5159789, 'lon': -65.5897525}}, 'PRK': {'sw': {'lat': 37.5867855, 'lon': 124.0913902}, 'ne': {'lat': 43.0089642, 'lon': 130.924647}}, 'PRT': {'sw': {'lat': 29.8288021, 'lon': -31.5575303}, 'ne': {'lat': 42.1543112, 'lon': -6.1891593}}, 'PRY': {'sw': {'lat': -27.6063935, 'lon': -62.6442036}, 'ne': {'lat': -19.2876472, 'lon': -54.258}}, 'PSE': {'sw': {'lat': 31.2201289, 'lon': 34.0689732}, 'ne': {'lat': 32.5521479, 'lon': 35.5739235}}, 'PYF': {'sw': {'lat': -28.0990232, 'lon': -154.9360599}, 'ne': {'lat': -7.6592173, 'lon': -134.244799}}, 'QAT': {'sw': {'lat': 24.4707534, 'lon': 50.5675}, 'ne': {'lat': 26.3830212, 'lon': 52.638011}}, 'REU': {'sw': {'lat': -21.3897308, 'lon': 55.2164268}, 'ne': {'lat': -20.8717136, 'lon': 55.8366924}}, 'ROU': {'sw': {'lat': 43.618682, 'lon': 20.2619773}, 'ne': {'lat': 48.2653964, 'lon': 30.0454257}}, 'RUS': {'sw': {'lat': 41.1850968, 'lon': 19.6389}, 'ne': {'lat': 82.0586232, 'lon': 180}}, 'RWA': {'sw': {'lat': -2.8389804, 'lon': 28.8617546}, 'ne': {'lat': -1.0474083, 'lon': 30.8990738}}, 'SAU': {'sw': {'lat': 16.29, 'lon': 34.4571718}, 'ne': {'lat': 32.1543377, 'lon': 55.6666851}}, 'SDN': {'sw': {'lat': 8.685278, 'lon': 21.8145046}, 'ne': {'lat': 22.224918, 'lon': 39.0576252}}, 'SEN': {'sw': {'lat': 12.2372838, 'lon': -17.7862419}, 'ne': {'lat': 16.6919712, 'lon': -11.3458996}}, 'SGP': {'sw': {'lat': 1.1304753, 'lon': 103.6920359}, 'ne': {'lat': 1.4504753, 'lon': 104.0120359}}, 'SGS': {'sw': {'lat': -59.684, 'lon': -42.354739}, 'ne': {'lat': -53.3500755, 'lon': -25.8468303}}, 'SHN': {'sw': {'lat': -16.23, 'lon': -5.9973424}, 'ne': {'lat': -15.704, 'lon': -5.4234153}}, 'SJM': {'sw': {'lat': 70.6260825, 'lon': -9.6848146}, 'ne': {'lat': 81.028076, 'lon': 34.6891253}}, 'SLB': {'sw': {'lat': -13.2424298, 'lon': 155.3190556}, 'ne': {'lat': -4.81085, 'lon': 170.3964667}}, 'SLE': {'sw': {'lat': 6.755, 'lon': -13.5003389}, 'ne': {'lat': 9.999973, 'lon': -10.271683}}, 'SLV': {'sw': {'lat': 12.976046, 'lon': -90.1790975}, 'ne': {'lat': 14.4510488, 'lon': -87.6351394}}, 'SMR': {'sw': {'lat': 43.8937002, 'lon': 12.4033246}, 'ne': {'lat': 43.992093, 'lon': 12.5160665}}, 'SOM': {'sw': {'lat': -1.8031969, 'lon': 40.98918}, 'ne': {'lat': 12.1889121, 'lon': 51.6177696}}, 'SPM': {'sw': {'lat': 46.5507173, 'lon': -56.6972961}, 'ne': {'lat': 47.365, 'lon': -55.9033333}}, 'SRB': {'sw': {'lat': 42.2322435, 'lon': 18.8142875}, 'ne': {'lat': 46.1900524, 'lon': 23.006309}}, 'STP': {'sw': {'lat': -0.2135137, 'lon': 6.260642}, 'ne': {'lat': 1.9257601, 'lon': 7.6704783}}, 'SUR': {'sw': {'lat': 1.8312802, 'lon': -58.070833}, 'ne': {'lat': 6.225, 'lon': -53.8433358}}, 'SVK': {'sw': {'lat': 47.7314286, 'lon': 16.8331891}, 'ne': {'lat': 49.6138162, 'lon': 22.56571}}, 'SVN': {'sw': {'lat': 45.4214242, 'lon': 13.3754696}, 'ne': {'lat': 46.8766816, 'lon': 16.5967702}}, 'SWE': {'sw': {'lat': 55.1331192, 'lon': 10.5930952}, 'ne': {'lat': 69.0599699, 'lon': 24.1776819}}, 'SWZ': {'sw': {'lat': -27.3175201, 'lon': 30.7908}, 'ne': {'lat': -25.71876, 'lon': 32.1349923}}, 'SYC': {'sw': {'lat': -10.4649258, 'lon': 45.9988759}, 'ne': {'lat': -3.512, 'lon': 56.4979396}}, 'SYR': {'sw': {'lat': 32.311354, 'lon': 35.4714427}, 'ne': {'lat': 37.3184589, 'lon': 42.3745687}}, 'TCA': {'sw': {'lat': 20.9553418, 'lon': -72.6799046}, 'ne': {'lat': 22.1630989, 'lon': -70.8643591}}, 'TCD': {'sw': {'lat': 7.44107, 'lon': 13.47348}, 'ne': {'lat': 23.4975, 'lon': 24.0}}, 'TGO': {'sw': {'lat': 5.926547, 'lon': -0.1439746}, 'ne': {'lat': 11.1395102, 'lon': 1.8087605}}, 'THA': {'sw': {'lat': 5.612851, 'lon': 97.3438072}, 'ne': {'lat': 20.4648337, 'lon': 105.636812}}, 'TJK': {'sw': {'lat': 36.6711153, 'lon': 67.3332775}, 'ne': {'lat': 41.0450935, 'lon': 75.1539563}}, 'TKL': {'sw': {'lat': -9.6442499, 'lon': -172.7213673}, 'ne': {'lat': -8.3328631, 'lon': -170.9797586}}, 'TKM': {'sw': {'lat': 35.129093, 'lon': 52.335076}, 'ne': {'lat': 42.7975571, 'lon': 66.6895177}}, 'TLS': {'sw': {'lat': -9.5642775, 'lon': 124.0415703}, 'ne': {'lat': -8.0895459, 'lon': 127.5335392}}, 'TON': {'sw': {'lat': -24.1034499, 'lon': -179.3866055}, 'ne': {'lat': -15.3655722, 'lon': -173.5295458}}, 'TTO': {'sw': {'lat': 9.8732106, 'lon': -62.083056}, 'ne': {'lat': 11.5628372, 'lon': -60.2895848}}, 'TUN': {'sw': {'lat': 30.230236, 'lon': 7.5219807}, 'ne': {'lat': 37.7612052, 'lon': 11.8801133}}, 'TUR': {'sw': {'lat': 35.8076804, 'lon': 25.6212891}, 'ne': {'lat': 42.297, 'lon': 44.8176638}}, 'TUV': {'sw': {'lat': -9.9939389, 'lon': 175.1590468}, 'ne': {'lat': -5.4369611, 'lon': 178.7344938}}, 'TWN': {'sw': {'lat': 10.374269, 'lon': 114.3599058}, 'ne': {'lat': 26.4372222, 'lon': 122.297}}, 'TZA': {'sw': {'lat': -11.761254, 'lon': 29.3269773}, 'ne': {'lat': -0.9854812, 'lon': 40.6584071}}, 'UGA': {'sw': {'lat': -1.4823179, 'lon': 29.573433}, 'ne': {'lat': 4.2340766, 'lon': 35.000308}}, 'UKR': {'sw': {'lat': 44.184598, 'lon': 22.137059}, 'ne': {'lat': 52.3791473, 'lon': 40.2275801}}, 'UMI': {'sw': {'lat': 6.1779744, 'lon': -162.6816297}, 'ne': {'lat': 6.6514388, 'lon': -162.1339885}}, 'URY': {'sw': {'lat': -35.7824481, 'lon': -58.4948438}, 'ne': {'lat': -30.0853962, 'lon': -53.0755833}}, 'USA': {'sw': {'lat': 24.9493, 'lon': -125.0011}, 'ne': {'lat': 49.5904, 'lon': -66.9326}}, 'UZB': {'sw': {'lat': 37.1821164, 'lon': 55.9977865}, 'ne': {'lat': 45.590118, 'lon': 73.1397362}}, 'VAT': {'sw': {'lat': 41.9002044, 'lon': 12.4457442}, 'ne': {'lat': 41.9073912, 'lon': 12.4583653}}, 'VCT': {'sw': {'lat': 12.5166548, 'lon': -61.6657471}, 'ne': {'lat': 13.583, 'lon': -60.9094146}}, 'VEN': {'sw': {'lat': 0.647529, 'lon': -73.3529632}, 'ne': {'lat': 15.9158431, 'lon': -59.5427079}}, 'VGB': {'sw': {'lat': 17.623468, 'lon': -65.159094}, 'ne': {'lat': 18.464984, 'lon': -64.512674}}, 'VIR': {'sw': {'lat': 17.623468, 'lon': -65.159094}, 'ne': {'lat': 18.464984, 'lon': -64.512674}}, 'VNM': {'sw': {'lat': 8.1790665, 'lon': 102.14441}, 'ne': {'lat': 23.393395, 'lon': 114.3337595}}, 'VUT': {'sw': {'lat': -20.4627425, 'lon': 166.3355255}, 'ne': {'lat': -12.8713777, 'lon': 170.449982}}, 'WLF': {'sw': {'lat': -14.5630748, 'lon': -178.3873749}, 'ne': {'lat': -12.9827961, 'lon': -175.9190391}}, 'WSM': {'sw': {'lat': -14.2770916, 'lon': -173.0091864}, 'ne': {'lat': -13.2381892, 'lon': -171.1929229}}, 'YEM': {'sw': {'lat': 11.9084802, 'lon': 41.60825}, 'ne': {'lat': 19.0, 'lon': 54.7389375}}, 'ZAF': {'sw': {'lat': -47.1788335, 'lon': 16.3335213}, 'ne': {'lat': -22.1250301, 'lon': 38.2898954}}, 'ZMB': {'sw': {'lat': -18.0765945, 'lon': 21.9993509}, 'ne': {'lat': -8.2712822, 'lon': 33.701111}}, 'ZWE': {'sw': {'lat': -22.4241096, 'lon': 25.2373}, 'ne': {'lat': -15.6097033, 'lon': 33.0683413}}}
boxes = json.loads(json.dumps(boxes))
2.3. Dividing the countries into 1 km-sq boxes¶
In this section, the countries' bounding boxes were divided into 1 km-sq boxes based on convention. Each of the nodes of a country was, then, assigned to one of these boxes where it falls into. We defined a function grid which returned a string of the form WID-LEN, where WID corresponds to the column number of the box and LEN corresponds to the row number of the box.
In [12]:
from math import sin, cos, sqrt, atan2, radians
def km(lone, lonw, latn, lats):
''' Returns the distance in kilometers between two points given their
longitude and latitude.
Parameters:
-----------
lone, latn: float
Longitude and latitude of one of the points
lonw, lats: float
Longitude and latitude of one of the points
Returns:
--------
distance: float
The distance in kilometers between the two points
'''
lat1 = radians(lats)
lon1 = radians(lone)
lat2 = radians(latn)
lon2 = radians(lonw)
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
c = 2 * atan2(sqrt(a), sqrt(1 - a))
R = 6373.0
distance = R * c
return distance
def grid(lat, lon, country):
'''Returns the grid box of a node.
This will assign nodes in a country into a box where they fall into. These
boxes are formed by dividing the country into 1 km-square boxes.
Parameters:
-----------
lat: float
Latitude of a node
lon: float
Longitude of a node
country: string
Alpha-3 code of a country as described in ISO 3166
Returns:
--------
box: string
Box number of the form WID-LEN where WID is the column number of a box
and LEN the row number.
'''
country_box = boxes[country]
lat1 = country_box['sw']['lat']
lon1 = country_box['ne']['lon']
lat2 = country_box['ne']['lat']
lon2 = country_box['sw']['lon']
width = km(lon1, lon2, lat2, lat2)
newpt = tuple((lat, lon))
width_to_star = km(lon2, newpt[1], lat2, lat2)
len_to_star = km(lon2, lon2, lat2, newpt[0])
widthbase = round(width_to_star)
lenbase = round(len_to_star)
box = str(widthbase) + '-' + str(lenbase)
return box
In [13]:
import dask.dataframe as dd
In [14]:
# List of ASEAN countries
countries = ['PHL', 'SGP', 'MYS', 'IDN',
'LAO', 'KHM', 'BRN', 'MMR', 'THA', 'VNM']
In [6]:
# # add amenity names as new columns and save as parquet
# for country in countries:
# df = dd.read_csv('athena-query_results/amenities_{}.csv'.format(country))
# # node_type
# df['amenity'] = df.apply(lambda x: amenity(x.tags),
# meta=(None, 'object'), axis=1)
# # save only relevant columns
# df_save = df[['lat', 'lon', 'amenity']].copy()
# df_save.to_csv('wrangled/{}*.csv'.format(country))
In [15]:
# # read parquets, add amenity types and grid columns, overwrite parquets with
# # updated dataframe
# for country in countries:
# df = dd.read_csv('wrangled/{}0.csv'.format(country))
# # amenity_type
# df['amenity_type'] = df.apply(lambda x: amen_type(x.amenity),
# meta=(None, 'object'), axis=1);
# df['grid'] = df.apply(lambda x: grid(x.lat, x.lon, country),
# meta=(None, 'object'),axis=1);
# # save only relevant columns
# df_save = df[['grid', 'amenity', 'amenity_type']].copy()
# df_save.to_parquet('wrangled/{}.parquet'.format(country))
3. Association Rule Mining¶
In this step, we mined the following association rules. These rules were chosen as their association were deemed significant to a country's economic health. Each row in the table below is called an association rule. An association rule consists of antecedent and consequent and its level of association is measured using confidence. For example, an association rule with a confidence of 97% means that there's a 97% probability that a consequent appears given an antecedent. The first rule below, says that we want to measure the probability of having a transportation node given that there's a education node in a vicinity.
| Antecedent | Consequent |
|---|---|
| Education | Transportation |
| Health | Transportation |
| Law | Transportation |
| Entertainment | Transportation |
| Education, Health, Entertainment | Transportation |
| Education | Safety |
| Transportation | Safety |
| Financial | Safety |
| Education, Transportation, Financial | Safety |
| Education | Health |
| Religious | Health |
| Entertainment | Health |
| Law | Health |
| Education, Entertainment | Health |
| Education | Financial |
| Health | Financial |
| Entertainment | Financial |
| Education, Health, Entertainment | Financial |
In [16]:
# rules to mine
antecedents = [{'education'}, {'health'}, {'law'}, {'entertainment'},
{'education', 'health', 'entertainment'}, {'education'},
{'transportation'}, {'financial'},
{'education', 'transportation', 'financial'}, {'education'},
{'religious'}, {'entertainment'}, {'law'},
{'education', 'entertainment'}, {'education'}, {'health'},
{'entertainment'}, {'education', 'health', 'entertainment'}]
consequents = [{'transportation'}, {'transportation'}, {'transportation'},
{'transportation'}, {'transportation'}, {'safety'}, {'safety'},
{'safety'}, {'safety'}, {'health'}, {'health'}, {'health'},
{'health'}, {'health'}, {'financial'}, {'financial'},
{'financial'}, {'financial'}]
rule_pairs = [(a, c) for a, c in zip(antecedents, consequents)]
3.1. Association rule mining using pyspark's FPGrowth¶
FPGrowth which is an algorithm builds a compact-tree structure and uses the tree for frequent itemset mining and generating rules shall be used in this study. An advantage of using FPGrowth compared to other algorithms such as Apriori and ECLAT is that it does not generate candidate itemsets which is not part of the database thus leading to a faster and more efficient association rule mining.
In [17]:
from pyspark.sql.functions import collect_set
from pyspark.ml.fpm import FPGrowth
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
In [18]:
import numpy as np
rules_confidence = {'Rules': rule_pairs}
for country in countries:
parquet_df = spark.read.parquet('wrangled/{}.parquet'.format(country))
df = parquet_df.select('grid', 'amenity', 'amenity_type')
txdf = df.groupby("grid").agg(collect_set("amenity_type"))
fpGrowth = FPGrowth(itemsCol="collect_set(amenity_type)", minSupport=0.01,
minConfidence=0.01)
model = fpGrowth.fit(txdf)
ar = model.associationRules.toPandas()
ar.antecedent = ar.antecedent.map(lambda x: set(x))
ar.consequent = ar.consequent.map(lambda x: set(x))
confidence = []
for rule in rule_pairs:
try:
confidence.append(ar[(ar.antecedent == rule[0]) & (
ar.consequent == rule[1])].values[0][2])
except:
confidence.append(np.nan)
rules_confidence[country] = confidence
In [19]:
# pickle the results
import pickle
import pandas as pd
with open('asean_conf.pickle', 'wb') as f:
pickle.dump(pd.DataFrame(rules_confidence), f)
4. Economic Referencing¶
After mining the association rules, we compare those to current relative standings of countries in the ASEAN and attempted to establish relationships between those.
We used relative standings of countries as shown in the dataframe below:
In [5]:
indi = pd.read_excel('indicators.xlsx')
indi = indi.rename(columns={'BRUNEI':"BRN",'CAMBODIA':"KHM",
'LAOS':"LAO",'INDONESIA':'IDN',
'MYANMAR':"MMR", 'MALAYSIA':"MYS",
'PHILIPPINES':"PHL",'SINGAPORE':"SGP",
'THAILAND':"THA",'VIETNAM':"VNM"})
indi
Out[5]:
In [6]:
import pandas as pd
import pickle
import pandas as pd
with open('asean_conf.pickle', 'rb') as f:
asean_amen = pickle.load(f)
In [7]:
join = pd.concat([indi,asean_amen], axis=0, sort=False)
# join.head(10)
In [8]:
join.iloc[0,8] = 'Adult Literacy Rate'
join.iloc[1,8] = 'Ease of Doing Business'
join.iloc[2,8] = 'Competitiveness level'
join.iloc[3,8] = 'Life Expectancy'
join.iloc[4,8] = 'Life Expectancy (Male)'
join.iloc[5,8] = 'Life Expectancy (Female)'
join.iloc[6,8] = 'GDP per capita'
join.iloc[7,8] = 'Human Development Index'
join = join.drop(['Relevant SocioEconomic Indicator'], axis=1)
In [9]:
join = join.set_index(['Rules'])
In [12]:
# join
In [26]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">RESULTS</span></div>
We are interested in finding association rules related to four major amenity types -- financial, health, transportation, and safety. We have uncovered high confidence for all these amenity types except the latter.
In [128]:
import pandas as pd
import pickle
import pandas as pd
with open('asean_conf.pickle', 'rb') as f:
asean_conf = pickle.load(f)
asean_conf
Out[128]:
In [4]:
import matplotlib.pyplot as plt
In [5]:
# education, health, entertainment -> financial
EHEF = asean_conf.iloc[17][1:]
EHEF = pd.DataFrame(EHEF.sort_values(ascending=False)).reset_index()
EHEF.columns = ['country','conf']
EHEF = EHEF.head()
In [6]:
import plotly.graph_objects as go
fig = go.Figure([go.Bar(x=EHEF.country, y=EHEF.conf)])
fig.update_layout(
title="Figure 1: Antecedent: Education, Health, Entertainment | Consequent: Financial",
xaxis_title="Country",
yaxis_title="Confidence")
fig.update_traces(marker_color='rgb(253,183,19)')
In [7]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Among 10 ASEAN Countries, the highest chance that one can find a Financial amenity near Educational, Health and Entertainment facilities
is at Singapore. Said country is followed by Vietnam and Cambodia. This information is useful for travellers who are in need of cash during
trips in these countries.
<div>
In [8]:
# education, entertainment -> health
EEH = asean_conf.iloc[13][1:]
EEH = pd.DataFrame(EEH.sort_values(ascending=False)).reset_index()
EEH.columns = ['country','conf']
EEH = EEH.head()
In [9]:
import plotly.graph_objects as go
fig = go.Figure([go.Bar(x=EEH.country, y=EEH.conf)])
fig.update_layout(
title="Figure 2: Antecedent: Education, Entertainment | Consequent: Health",
xaxis_title="Country",
yaxis_title="Confidence")
fig.update_traces(marker_color='rgb(253,183,19)')
In [10]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Singapore is still the best country with the highest confidence of situating a health facility near Education and Entertainment nodes.
Countries such as Myanmar (with 55% confidence) should learn from Singapore to better plan their cities and make sure that health facilities are well distributed.
<div>
In [11]:
# transportation -> safety
TS = asean_conf.iloc[6][1:]
TS = pd.DataFrame(TS.sort_values(ascending=False)).reset_index()
TS.columns = ['country','conf']
TS = TS.head()
In [12]:
import plotly.graph_objects as go
fig = go.Figure([go.Bar(x=TS.country, y=TS.conf)])
fig.update_layout(
title="Figure 3: Antecedent: Transportation | Consequent: Safety",
xaxis_title="Country",
yaxis_title="Confidence")
fig.update_traces(marker_color='rgb(253,183,19)')
In [13]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Singapore is consistently the country with the best confidence in terms of safety and transportation facilities tandem.
The Philippines and Malaysia came in next however, we can see a large disparity in terms of confidence. As such, these
countries should still work on providing ample safety facilities nearby transport nodes that are heavy with foot traffic.
<div>
In [14]:
# education, health, entertainment -> transportation
EHET = asean_conf.iloc[4][1:]
EHET = pd.DataFrame(EHET.sort_values(ascending=False)).reset_index()
EHET.columns = ['country','conf']
EHET = EHET.head()
In [15]:
import plotly.graph_objects as go
fig = go.Figure([go.Bar(x=EHET.country, y=TS.conf)])
fig.update_layout(
title="Figure 4: Antecedent: Education, Health, Entertainment | Consequent: Transportation",
xaxis_title="Country",
yaxis_title="Confidence")
fig.update_traces(marker_color='rgb(253,183,19)')
In [118]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Here, we can see that only Singapore is above 50% in terms of providing a transportation facility nearby Education, Health and Entertainment nodes.
Per existing studies, transportation is one of the best indicators of development. Hence, it is crucial that other ASEAN countries consider augmenting their transport facilities and strategically situate those near other crucial nodes such as education and health.
<div>
In [119]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
Figure 5. Link between Relative Standing to Relative Confidence Scores of Amenity Associations
<div>
In [120]:
from IPython.display import Image
Image(filename='referencing.png')
Out[120]:
In [2]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
We found that 'Entertainment' is the common antecedent of amenity associations related to
Economic Indicators. This is different from our original assumption that social amenities such as those related to education
and health will most likely be the antecedents of such associations. With this, it may be suggested that urban planners may look into
entertainment as a central node for other amenity developments.
<br>
<br>
<br>
<div>
In [27]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">CONCLUSION</span></div>
In [18]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
We recommend developing countries to look into township development and its contribution to an area’s economic development, be it urban or rural. As for the Philippines, we suggest that the government focus on improving the quality of life of its citizens by providing ample and decent amenities nearby entertainment centers for this is linked to improving growth.
<div>
In [28]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div><span style="background-color: #171309; padding-top: 100px; padding-right: 20px; padding-bottom: 5px; padding-left: 20px; color: #fdb713; font-size: 20px; font-weight: bold">REFERENCES</span></div>
In [129]:
%%html
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>
<style>
body {
font-family: 'Roboto', 'Helvetica', sans-serif;
font-size: 16px;
}
</style>
<div align='justify'>
The analysis of different economic growth rates among countries. Available at https://faculty.washington.edu/karyiu/confer/beijing06/papers/song-li.pdf <br>
<br>Amenities and the Social Structure of Cities. Available at https://www.gate.cnrs.fr/IMG/pdf/moizeau_gmrt.pdf <br>
<br>Amenities and Community Economic Development: Strategies for Sustainability (2001). Available at http://www.jrap-journal.org/pastvolumes/2000/v31/31-2-5.pdf <br>
<br>The Role of Amenities and Quality of Life In Rural Economic Growth. Available at https://naldc-legacy.nal.usda.gov/naldc/download.xhtml?id=41988&content=PDF <br>
<br>Statistical grids for Norway. Documentation of national grids for analysis and visualisation of spatial data in Norway. Availble at https://www.researchgate.net/publication/301613184_Statistical_grids_for_Norway_Documentation_of_national_grids_for_analysis_and_visualisation_of_spatial_data_in_Norway <br>
<br>UK Emission Mapping Methodology 2011 A report of the National Atmospheric Emissions Inventory. Available at https://uk-air.defra.gov.uk/assets/documents/reports/cat07/1403100909_UK_Emission_Mapping_Methodology_2011-Issue_1.pdf <br>
<br>GSP Guidelines for sharing national data/information to compile a Global Soil Organic Carbon (GSOC) map. Available at http://www.fao.org/3/a-bp164e.pdf <br><br>
Adult literacy rate is defined as the percentage of population aged 15 years and over who can both read and write with understanding a short simple statement on his/her everyday life.
https://www.aseanstats.org/wp-content/uploads/2018/12/ASEAN-Key-Figures-2018.pdf <br><br>
GDP from ASEAN stats - https://www.aseanstats.org/wp-content/uploads/2018/10/ASEAN_Statistical_Leaflet_2018.pdf <br><br>
Competitiveness Index - http://www3.weforum.org/docs/WEF_TheGlobalCompetitivenessReport2019.pdf <br><br>
Life Expectancy - http://worldpopulationreview.com/countries/life-expectancy-by-country/ <br><br>
Ease of Doing Business - https://www.doingbusiness.org/content/dam/doingBusiness/media/Fact-Sheets/DB19/FactSheet_DoingBusiness2019_EAP_Eng.pdf<br><br>
Amenity-led Change in Rural Towns and Regions - http://selkirk.ca/sites/default/files/RIC/selkirk-college-innovation-ric-planning-for-amenity-led-change.pdf <br><br>
Building A Deep Learning Model using Keras - https://towardsdatascience.com/building-a-deep-learning-model-using-keras-1548ca149d37 <br><br>
In [ ]: